﻿using Apps.Models;
using Apps.Models.PLAN;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.PLAN
{
    public partial class PLAN_PRODUCTION_DEMANDRepository
    {
        public IQueryable<PLAN_PRODUCTION_DEMANDModel> QueryProductionDemandList()
        {
            //PLAN_PRODUCTION
            //string sql = string.Format(@"SELECT * FROM ( 
            //                                  SELECT A.MONTH ,Power_Plan ,Heat_Plan ,Power_Standard ,Heating_Standard ,Crew_Name ,A.Production_Id ,Crew_Id ,COAL_DEMAND   
            //                                  FROM PLAN_PRODUCTION A
            //                                  LEFT JOIN (
            //                                    SELECT C.Crew_Id,C.Production_Id,SUM(C.Power_Plan) Power_Plan,SUM(C.Heat_Plan) Heat_Plan,
            //                                    C.Power_Standard,C.Heating_Standard,D.Crew_Name,C.COAL_DEMAND 
            //                                    FROM PLAN_PRODUCTION_DEMAND C 
            //                                    LEFT JOIN PUB_CREW D ON C.Crew_Id=D.Crew_Id 
            //                                    GROUP BY C.Crew_Id,C.Production_Id,C.Power_Standard,C.Heating_Standard,D.Crew_Name,C.COAL_DEMAND
            //                                  ) B ON A.Production_Id=B.Production_Id
            //                                ) ");

            var result = from A in Context.PLAN_PRODUCTION
                         join B in (
                             from C in Context.PLAN_PRODUCTION_DEMAND
                             join D in Context.PUB_CREW on C.CREW_ID equals D.CREW_ID into crew
                             from TD in crew.DefaultIfEmpty()
                             group new { C, TD } by new
                             {
                                 C.CREW_ID,
                                 C.PRODUCTION_ID,
                                 C.POWER_STANDARD,
                                 C.HEATING_STANDARD,
                                 C.COAL_DEMAND,
                                 TD.CREW_NAME,
                                 TD.IS_HEATING
                             } into g1
                             select new
                             {
                                 g1.Key.CREW_ID,
                                 g1.Key.PRODUCTION_ID,
                                 g1.Key.POWER_STANDARD,
                                 g1.Key.HEATING_STANDARD,
                                 g1.Key.CREW_NAME,
                                 g1.Key.COAL_DEMAND,
                                 g1.Key.IS_HEATING,
                                 Power_Plan = g1.Sum(m => m.C.POWER_PLAN),
                                 Heat_Plan = g1.Sum(m => m.C.HEAT_PLAN),
                             }
                         ) on A.PRODUCTION_ID equals B.PRODUCTION_ID
                         select new PLAN_PRODUCTION_DEMANDModel()
                         {
                             MONTH = A.MONTH,
                             POWER_PLAN = B.Power_Plan,
                             HEAT_PLAN = B.Heat_Plan,
                             POWER_STANDARD = B.POWER_STANDARD,
                             HEATING_STANDARD = B.HEATING_STANDARD,
                             CREW_NAME = B.CREW_NAME,
                             IS_HEATING= B.IS_HEATING,
                             PRODUCTION_ID = A.PRODUCTION_ID,
                             CREW_ID = B.CREW_ID,
                             COAL_DEMAND = B.COAL_DEMAND
                         };

            return result;
            //A.MONTH ,Power_Plan ,Heat_Plan ,Power_Standard ,Heating_Standard ,Crew_Name ,A.Production_Id ,Crew_Id ,COAL_DEMAND
            //throw new NotImplementedException();
        }
    }
}
